/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package program.database;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import org.postgresql.largeobject.LargeObject;
import org.postgresql.largeobject.LargeObjectManager;
import program.beans.Bill;
import program.beans.BillAmount;
import program.beans.Category;
import program.beans.Company;
import program.properties.PropertyManager;

/**
 *
 * @author Bernhard
 *
 *
 *
 *Responsible: Bernhard, Patrick, Johannes
 */
public class Database {

    private static Database instance;
    private Connection con;
    private PropertyManager pm = PropertyManager.getInstance();

    private Database() {
        try {
            Class.forName("org.postgresql.Driver");
        } catch (Exception ex) {
                        JOptionPane.showMessageDialog(null, ex);

        }
    }

    private void connect() throws Exception {
        con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/vorsteuer", "postgres", "postgres");
    }

    public void createDB() throws Exception {
        con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/", "postgres", "postgres");


        String sql = "CREATE DATABASE vorsteuer;";

        Statement st = con.createStatement();

//        st.execute(sql);

        con.close();
        con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/vorsteuer", "postgres", "postgres");
        st = con.createStatement();

        sql = "";

        sql = "CREATE SEQUENCE comp_seq; CREATE SEQUENCE cat_seq;"
                + "CREATE TABLE company"
                + "("
                + " compid integer NOT NULL DEFAULT nextval('comp_seq'::regclass),"
                + "compname text NOT NULL,"
                + " CONSTRAINT company_pkey PRIMARY KEY (compid )"
                + ");";

       st.execute(sql);
        sql = "CREATE TABLE category"
                + "("
                + "cid integer NOT NULL DEFAULT nextval('cat_seq'::regclass),"
                + " description text NOT NULL,"
                + " CONSTRAINT category_pkey PRIMARY KEY (cid )"
                + ");";

        st.execute(sql);

        sql = "CREATE TABLE bill"
                + "("
                + " \"BID\" timestamp without time zone NOT NULL DEFAULT now(),"
                + " \"BillDate\" date NOT NULL,"
                + "\"CompID\" integer NOT NULL,"
                + " \"Type\" integer NOT NULL DEFAULT 0,"
                + " \"CatID\" integer NOT NULL,"
                + " CONSTRAINT bill_pk PRIMARY KEY (\"BID\" ),"
                + " CONSTRAINT bill_cat FOREIGN KEY (\"CatID\")"
                + "     REFERENCES category (cid) MATCH SIMPLE"
                + "     ON UPDATE NO ACTION ON DELETE NO ACTION,"
                + " CONSTRAINT bill_ibfk_1 FOREIGN KEY (\"CompID\")"
                + "     REFERENCES company (compid) MATCH SIMPLE"
                + "     ON UPDATE NO ACTION ON DELETE NO ACTION"
                + ")";

        st.execute(sql);

        sql = "CREATE TABLE billamount"
                + "("
                + " bid timestamp without time zone NOT NULL,"
                + "amount numeric NOT NULL,"
                + "\"Percentage\" numeric NOT NULL,"
                + " CONSTRAINT billamount_pk PRIMARY KEY (bid , amount ,\"Percentage\" ),"
                + " CONSTRAINT billamount_ibfk_1 FOREIGN KEY (bid)"
                + "  REFERENCES bill (\"BID\") MATCH SIMPLE"
                + " ON UPDATE NO ACTION ON DELETE NO ACTION"
                + ")";

        st.execute(sql);

        sql = "CREATE TABLE billcopy"
                + "("
                + " billdata oid,"
                + " bid timestamp without time zone NOT NULL,"
                + " CONSTRAINT billcopy_pkey PRIMARY KEY (bid )"
                + ")";

        st.execute(sql);

        con.commit();
        con.close();

    }

    public void disconnect() throws Exception {
        con.close();
    }

    public static Database getDatabase() {
        if (instance == null) {
            instance = new Database();
        }

        return instance;
    }

    public String getNextCompany(String beginning) throws Exception {
        String company = "";
        this.connect();
        beginning = beginning + "%";
        String sql = "SELECT compname FROM company WHERE compname LIKE ? ORDER BY compname ASC";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, beginning);

        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            company = rs.getString(1);
        }

        this.disconnect();
        return company;
    }

    public ArrayList<Company> getAllCompanies() throws Exception {
        this.connect();
        ArrayList<Company> companies = new ArrayList<>();
        String sql = "SELECT * FROM company ORDER BY compname";
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);

        while (rs.next()) {
            int compid = rs.getInt("compid");
            String compname = rs.getString("compname");

            Company c = new Company(compid, compname);
            companies.add(c);
        }

        this.disconnect();
        return companies;
    }

    public int addCompany(String compname) throws Exception {
        this.connect();
        String sql = "INSERT INTO company (compname) VALUES (?);";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, compname);
        ps.execute();

        sql = "SELECT MAX(compid) FROM company";
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        int compid = 0;
        if (rs.next()) {
            compid = rs.getInt(1);
        }

        this.disconnect();
        return compid;
    }

    public void editCompany(Company comp) throws Exception {
        this.connect();
        String sql = "UPDATE company SET compname = ? WHERE compid = ?;";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, comp.getName());
        ps.setInt(2, comp.getId());
        ps.execute();
        this.disconnect();
    }

    public void deleteCompany(int compid) throws Exception {
        if (isCompanyInUse(compid)) {
            throw new Exception("Diese Firma kann nicht gelöscht werden, da Rechnungen mit ihr verbunden sind!");
        }
        this.connect();
        String sql = "DELETE FROM company WHERE compid = ?;";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1, compid);
        ps.execute();
        this.disconnect();
    }

    public ArrayList<Category> getAllCategories() throws Exception {
        this.connect();
        ArrayList<Category> categories = new ArrayList<>();
        String sql = "SELECT * FROM category ORDER BY description";
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);

        while (rs.next()) {
            int id = rs.getInt("cid");
            String description = rs.getString("description");
            Category c = new Category(id, description);
            categories.add(c);
        }

        this.disconnect();
        return categories;
    }

    public int addCategory(String description) throws Exception {
        this.connect();
        String sql = "INSERT INTO category (description) VALUES (?);";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, description);
        ps.execute();

        sql = "SELECT MAX(cid) FROM category";
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        int cid = 0;
        if (rs.next()) {
            cid = rs.getInt(1);
        }

        this.disconnect();
        return cid;
    }

    public void editCategory(Category cat) throws Exception {
        this.connect();
        String sql = "UPDATE category SET description = ? WHERE cid = ?;";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, cat.getDescription());
        ps.setInt(2, cat.getId());
        ps.execute();
        this.disconnect();
    }

    public void deleteCategory(int cid) throws Exception {
        if (isCategoryInUse(cid)) {
            throw new Exception("Diese Kategroie kann nicht gelöscht werden, da Rechnungen mit ihr verbunden sind!");
        }
        this.connect();
        String sql = "DELETE FROM category WHERE cid = ?;";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1, cid);
        ps.execute();
        this.disconnect();
    }

    public boolean isCompanyInUse(int compid) throws Exception {
        boolean is = false;
        connect();
        String sql = "SELECT COUNT(*) FROM bill WHERE \"CompID\" = " + compid;
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        rs.next();
        int count = rs.getInt(1);
        if (count > 0) {
            is = true;
        }
        disconnect();
        return is;
    }

    public boolean isCategoryInUse(int cid) throws Exception {
        boolean is = false;
        connect();
        String sql = "SELECT COUNT(*) FROM bill WHERE \"CatID\" = " + cid;
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);
        rs.next();
        int count = rs.getInt(1);
        if (count > 0) {
            is = true;
        }
        disconnect();
        return is;
    }

    public void addBill(Bill b) throws Exception {
        this.connect();
        String sql = "INSERT INTO bill (\"BID\", \"BillDate\", \"CompID\", \"Type\", \"CatID\")"
                + "VALUES(?,?,"
                + "(SELECT compid FROM company WHERE compname = ?"
                + "),?,(SELECT cid FROM category WHERE description = ?));";

        PreparedStatement ps = con.prepareStatement(sql);
        java.sql.Date date = new Date(b.getDate().getTime());

        ps.setTimestamp(1, b.getId());
        String compName = b.getComp().getName();
        ps.setDate(2, date);
        ps.setString(3, compName);
        ps.setInt(4, b.getType());
        String catDescription = b.getCat().getDescription();
        ps.setString(5, catDescription);

        ps.execute();


        sql = "";
        for (BillAmount ba : b.getAmounts()) {
            sql = "INSERT INTO billamount VALUES(?,?,?);";
            ps = con.prepareStatement(sql);
            ps.setTimestamp(1, b.getId());
            ps.setFloat(2, ba.getBrutto().floatValue());
            ps.setFloat(3, ba.getPercentage().floatValue());
            ps.execute();

        }

        sql = "";

        if (b.getBillCopy() != null) {

            sql = "INSERT INTO billcopy VALUES(lo_import(?),?);";
            ps = con.prepareStatement(sql);
            ps.setTimestamp(2, b.getId());
            ps.setString(1, b.getBillCopy().getAbsolutePath());
            
            ps.executeUpdate();
        }


        this.disconnect();
    }

    public ArrayList<Bill> getBills(Date from, Date since) throws Exception {
        ArrayList<Bill> bills = new ArrayList<>();
        String sql = "SELECT \"BID\", \"BillDate\", compid, compname, \"Type\", \"CatID\", description,billdata "
                + "FROM bill INNER JOIN category ON(bill.\"CatID\" = category.cid)"
                + " INNER JOIN company on(bill.\"CompID\" = company.\"compid\")"
                + " LEFT OUTER JOIN billcopy on(bill.\"BID\" = billcopy.\"bid\")"
                + "WHERE \"BillDate\" BETWEEN ? AND ? "
                + " ORDER BY bid DESC;";

        connect();
        System.out.println(sql);

        PreparedStatement ps = con.prepareStatement(sql);
        ps.setDate(1, new java.sql.Date(from.getTime()));
        ps.setDate(2, new java.sql.Date(since.getTime()));

        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Timestamp bid = rs.getTimestamp(1);
            System.out.println(rs.getTimestamp(1));
            java.sql.Date billdate = rs.getDate(2);
            int compid = rs.getInt(3);
            String compname = rs.getString(4);
            int type = rs.getInt(5);
            int catid = rs.getInt(6);
            String description = rs.getString(7);
            File bc = null;
            if (rs.getLong(8) > 0) {
                con.setAutoCommit(false);
                bc = File.createTempFile("pdf", ".pdf");
                LargeObjectManager lobj = ((org.postgresql.PGConnection) con).getLargeObjectAPI();
                String sql2 = "SELECT billdata FROM billcopy WHERE bid = ?";
                PreparedStatement ps2 = con.prepareStatement(sql2);
                ps2.setTimestamp(1, bid);
                ResultSet rs2 = ps2.executeQuery();
                rs2.next();
                long oid = rs2.getLong(1);
                LargeObject obj = lobj.open(oid, LargeObjectManager.READ);

                byte buff[] = new byte[obj.size()];
                obj.read(buff, 0, obj.size());
                FileOutputStream fos = new FileOutputStream(bc);
                fos.write(buff);

                fos.close();
                fos.close();
                con.setAutoCommit(true);
            }

            Bill b = new Bill(bid, billdate, new Company(compid, compname), null, bc, new Category(catid, description), type);
            bills.add(b);
            System.out.println("Bill: " + b);

        }

        for (Bill b : bills) {
            Timestamp bid = b.getId();
            sql = "SELECT amount, \"Percentage\" FROM billamount WHERE bid = ?";
            ps = con.prepareStatement(sql);
            ps.setTimestamp(1, bid);
            rs = ps.executeQuery();
            ArrayList<BillAmount> amounts = new ArrayList<>();
            while (rs.next()) {
                BigDecimal amount = new BigDecimal(rs.getDouble(1));
                BigDecimal perc = new BigDecimal(rs.getDouble(2));

                BillAmount ba = new BillAmount(bid, amount, perc);

                amounts.add(ba);
            }
            b.setAmounts(amounts);
        }

        disconnect();
        return bills;
    }

    public ArrayList<Bill> getBills(Date from, Date since, ArrayList<Category> cat) throws Exception {
        ArrayList<Bill> bills = new ArrayList<>();
        String sql = "SELECT \"BID\", \"BillDate\", compid, compname, \"Type\", \"CatID\", description,billdata "
                + "FROM bill INNER JOIN category ON(bill.\"CatID\" = category.cid)"
                + " INNER JOIN company on(bill.\"CompID\" = company.\"compid\")"
                + " LEFT OUTER JOIN billcopy on(bill.\"BID\" = billcopy.\"bid\")"
                + "WHERE \"BillDate\" BETWEEN ? AND ? AND \"CatID\" IN(#)"
                + " ORDER BY bid DESC;";

        connect();
        String catIN = "";
        for (Category c : cat) {
            catIN += c.getId() + ",";
        }
        catIN = catIN.substring(0, catIN.length() - 1);
        sql = sql.replaceAll("#", catIN);
        System.out.println("SQL: " + sql);
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setDate(1, new java.sql.Date(from.getTime()));
        ps.setDate(2, new java.sql.Date(since.getTime()));

        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            Timestamp bid = rs.getTimestamp(1);
            java.sql.Date billdate = rs.getDate(2);
            int compid = rs.getInt(3);
            String compname = rs.getString(4);
            int type = rs.getInt(5);
            int catid = rs.getInt(6);
            String description = rs.getString(7);
            File bc = null;
            if (rs.getLong(8) > 0) {
                con.setAutoCommit(false);
                bc = File.createTempFile("pdf", ".pdf");
                LargeObjectManager lobj = ((org.postgresql.PGConnection) con).getLargeObjectAPI();
                String sql2 = "SELECT billdata FROM billcopy WHERE bid = ?";
                PreparedStatement ps2 = con.prepareStatement(sql2);
                ps2.setTimestamp(1, bid);
                ResultSet rs2 = ps2.executeQuery();
                rs2.next();
                long oid = rs2.getLong(1);
                LargeObject obj = lobj.open(oid, LargeObjectManager.READ);

                byte buff[] = new byte[obj.size()];
                obj.read(buff, 0, obj.size());
                FileOutputStream fos = new FileOutputStream(bc);
                fos.write(buff);

                fos.close();
                fos.close();
                con.setAutoCommit(true);
            }

            Bill b = new Bill(bid, billdate, new Company(compid, compname), null, bc, new Category(catid, description), type);
            bills.add(b);
            System.out.println("Bill: " + b);

        }

        for (Bill b : bills) {
            Timestamp bid = b.getId();
            sql = "SELECT amount, \"Percentage\" FROM billamount WHERE bid = ?";
            ps = con.prepareStatement(sql);
            ps.setTimestamp(1, bid);
            rs = ps.executeQuery();
            ArrayList<BillAmount> amounts = new ArrayList<>();
            while (rs.next()) {
                BigDecimal amount = new BigDecimal(rs.getDouble(1));
                BigDecimal perc = new BigDecimal(rs.getDouble(2));

                BillAmount ba = new BillAmount(bid, amount, perc);

                amounts.add(ba);
            }
            b.setAmounts(amounts);
        }

        disconnect();
        return bills;
    }

    public void deleteBill(Bill b) throws Exception {
        this.connect();

        String sql = "DELETE FROM billamount WHERE bid = ?;";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setTimestamp(1, b.getId());
        ps.execute();
        sql = "DELETE FROM billcopy WHERE bid = ?;";
        ps = con.prepareStatement(sql);
        ps.setTimestamp(1, b.getId());
        ps.execute();
        sql = "DELETE FROM bill WHERE \"BID\" = ?;";
        ps = con.prepareStatement(sql);
        ps.setTimestamp(1, b.getId());
        ps.execute();

        this.disconnect();
    }

    public void editBill(Bill newBill) throws Exception {
        String sql = "UPDATE bill "
                + "SET \"BillDate\" = ?,"
                + " \"CompID\" = ?,"
                + " \"Type\" = ?,"
                + " \"CatID\"= ? "
                + "WHERE \"BID\" = ?;";
        connect();
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setDate(1, new java.sql.Date(newBill.getDate().getTime()));
        ps.setInt(2, newBill.getComp().getId());
        ps.setInt(3, newBill.getType());
        ps.setInt(4, newBill.getCat().getId());
        ps.setTimestamp(5, newBill.getId());

        ps.executeUpdate();
        sql = "DELETE FROM billamount WHERE bid = ?;";
        ps = con.prepareStatement(sql);
        ps.setTimestamp(1, newBill.getId());
        ps.execute();

        sql = "";
        for (BillAmount ba : newBill.getAmounts()) {
            sql = "INSERT INTO billamount VALUES(?,?,?);";
            ps = con.prepareStatement(sql);
            ps.setTimestamp(1, newBill.getId());
            ps.setFloat(2, ba.getBrutto().floatValue());
            ps.setFloat(3, ba.getPercentage().floatValue());
            ps.execute();

        }

        if (newBill.getBillCopy() != null) {
            sql = "UPDATE billcopy SET billdata = lo_import(?) WHERE bid = ?;";
            ps = con.prepareStatement(sql);
            ps.setString(1, newBill.getBillCopy().getAbsolutePath());
            ps.setTimestamp(2, newBill.getId());
            ps.executeUpdate();

        }


        disconnect();
    }

    public int getCatID(String description) throws Exception {
        connect();
        int i = 0;
        String sql = "SELECT cid FROM category WHERE description = ?";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, description);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            i = rs.getInt(1);
        }
        disconnect();
        return i;
    }

    public void testInsert() throws Exception {
        connect();
        File f = new File("test.pdf");
        String sql = "INSERT INTO test VALUES(lo_import(?));";
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1, f.getAbsolutePath());
        ps.execute();

        disconnect();
    }

    public void testExport() throws Exception {
        connect();
        con.setAutoCommit(false);
        File f = File.createTempFile("pdf", ".pdf");
        String sql = "SELECT bla FROM test WHERE ble = 25;";
        LargeObjectManager lobj = ((org.postgresql.PGConnection) con).getLargeObjectAPI();

        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        rs.next();
        long oid = rs.getLong(1);
        LargeObject obj = lobj.open(oid, LargeObjectManager.READ);

        byte buff[] = new byte[obj.size()];
        obj.read(buff, 0, obj.size());
        FileOutputStream fos = new FileOutputStream(f);
        fos.write(buff);


        System.out.println(f);
        disconnect();
    }

    public static void main(String[] args) throws Exception {
        Database db = getDatabase();
        db.createDB();
    }
}
